/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tces.controller;

import com.tces.model.SubjectSchedule;
import com.tces.model.SubjectStudent;
import com.tces.utils.ConnectionDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author arnolda
 */
public class SubjectStudentDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(SubjectScheduleDAO.class);
public static SubjectStudent find(int subjectStudentID) throws Exception {
    SubjectStudent subjectStudent=null;
        String sql = "SELECT\n"
                + "i.instructor_id,\n"
                + "concat_ws(\n"
                + "		' ',\n"
                + "		concat_ws(\n"
                + "			', ',\n"
                + "			i.instructor_last_name,\n"
                + "			i.instructor_first_name\n"
                + "		),\n"
                + "		i.instructor_middle_name\n"
                + "	) AS instructor_full_name,\n"
                + "concat_ws(\n"
                + "		' ',\n"
                + "		concat_ws(\n"
                + "			', ',\n"
                + "			s.student_last_name,\n"
                + "			s.student_first_name\n"
                + "		),\n"
                + "		s.student_middle_name\n"
                + "	) AS student_full_name,\n"
                + "ss.subject_schedule_id,\n"
                + "ss.subject_schedule,\n"
                + "ss.schedule_status,\n"
                + "ss.subject_id,\n"
                + "ss.subject_code,\n"
                + "sjt.subject_student_id,\n"
                + "s.student_id,\n"
                + "s.student_idno,\n"
                + "s.student_first_name,\n"
                + "s.student_middle_name,\n"
                + "s.student_last_name,\n"
                + "s.program_revision_id,\n"
                + "s.student_visible,\n"
                + "so.subject_offering_id,\n"
                + "so.subject_offering_name,\n"
                + "so.subejct_offering_sy_from,\n"
                + "so.subject_offering_sy_to,\n"
                + "so.subject_offering_semester,\n"
                + "so.subject_offering_status,\n"
                + "so.college_id,\n"
                + "program_revisions.program_revision_name,\n"
                + "program_revisions.program_revision_id,\n"
                + "programs.program_id,\n"
                + "programs.program_code,\n"
                + "c.college_id,\n"
                + "c.college_code,\n"
                + "c.college_name\n"
                + "FROM\n"
                + "subject_schedules AS ss\n"
                + "INNER JOIN subject_offerings AS so ON so.subject_offering_id = ss.subject_offering_id\n"
                + "INNER JOIN instructors AS i ON i.instructor_id = ss.instructor_id\n"
                + "INNER JOIN colleges AS c ON c.college_id = so.college_id\n"
                + "INNER JOIN subject_students AS sjt ON sjt.subject_schedule_id = ss.subject_schedule_id\n"
                + "INNER JOIN students AS s ON s.student_id = sjt.student_id\n"
                + "INNER JOIN program_revisions ON s.program_revision_id = program_revisions.program_revision_id\n"
                + "INNER JOIN programs ON program_revisions.program_id = programs.program_id\n"
                + "WHERE\n"
                + "so.subject_offering_status = 1 \n"
                + "AND s.student_visible=1\n"
                + "AND sjt.subject_student_id=?\n"
                + "ORDER BY ss.subject_code";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        preparedStatement.setInt(1, subjectStudentID);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        
        while (rs.next()) {
            subjectStudent = new SubjectStudent();
            subjectStudent.setSubjectStudentID(rs.getInt("subject_student_id"));
            subjectStudent.setSubjectOfferingID(rs.getInt("subject_offering_id"));
            subjectStudent.setSubjectScheduleID(rs.getInt("subject_schedule_id"));
            subjectStudent.setScheduleStatus(rs.getInt("schedule_status"));
            subjectStudent.setSubjectID(rs.getInt("subject_id"));
            subjectStudent.setInstructorID(rs.getInt("instructor_id"));
            subjectStudent.setSubjectOfferingName(rs.getString("subject_offering_name"));
            subjectStudent.setSubjectOfferingFullName(rs.getString("college_code") + " - " + rs.getString("subject_offering_name") + " - " + rs.getString("subject_code") + " : " + rs.getString("instructor_full_name"));
            subjectStudent.setInstructorFullName(rs.getString("instructor_full_name"));
            subjectStudent.setSubjectSchedule(rs.getString("subject_schedule"));
            subjectStudent.setSubjectCode(rs.getString("subject_code"));
//            subjectStudent.setInstructorFirstName(rs.getString("instructor_first_name"));
//            subjectStudent.setInstructorMiddleName(rs.getString("instructor_middle_name"));
//            subjectStudent.setInstructorLastName(rs.getString("instructor_last_name"));
            subjectStudent.setStudentID(rs.getInt("student_id"));
            subjectStudent.setProgramRevisionName(rs.getString("program_revision_name"));
            subjectStudent.setProgramCode(rs.getString("program_code"));
            subjectStudent.setStudentIDNumber(rs.getString("student_idno"));
//            subjectStudent.setStudentFirstName(rs.getString("student_first_name"));
//            subjectStudent.setStudentMiddleName(rs.getString("student_middle_name"));
//            subjectStudent.setStudentLastName(rs.getString("student_last_name"));
            subjectStudent.setStudentFullName(rs.getString("student_full_name"));
        }
        closeConnection(getConnectionObject());
        return subjectStudent;
    }
    public static List<SubjectStudent> findAll() throws Exception {
        List<SubjectStudent> classList = new ArrayList<SubjectStudent>();
        String sql = "SELECT\n"
                + "i.instructor_id,\n"
                + "concat_ws(\n"
                + "		' ',\n"
                + "		concat_ws(\n"
                + "			', ',\n"
                + "			i.instructor_last_name,\n"
                + "			i.instructor_first_name\n"
                + "		),\n"
                + "		i.instructor_middle_name\n"
                + "	) AS instructor_full_name,\n"
                + "concat_ws(\n"
                + "		' ',\n"
                + "		concat_ws(\n"
                + "			', ',\n"
                + "			s.student_last_name,\n"
                + "			s.student_first_name\n"
                + "		),\n"
                + "		s.student_middle_name\n"
                + "	) AS student_full_name,\n"
                + "ss.subject_schedule_id,\n"
                + "ss.subject_schedule,\n"
                + "ss.schedule_status,\n"
                + "ss.subject_id,\n"
                + "ss.subject_code,\n"
                + "sjt.subject_student_id,\n"
                + "s.student_id,\n"
                + "s.student_idno,\n"
                + "s.student_first_name,\n"
                + "s.student_middle_name,\n"
                + "s.student_last_name,\n"
                + "s.program_revision_id,\n"
                + "s.student_visible,\n"
                + "so.subject_offering_id,\n"
                + "so.subject_offering_name,\n"
                + "so.subejct_offering_sy_from,\n"
                + "so.subject_offering_sy_to,\n"
                + "so.subject_offering_semester,\n"
                + "so.subject_offering_status,\n"
                + "so.college_id,\n"
                + "program_revisions.program_revision_name,\n"
                + "program_revisions.program_revision_id,\n"
                + "programs.program_id,\n"
                + "programs.program_code,\n"
                + "c.college_id,\n"
                + "c.college_code,\n"
                + "c.college_name\n"
                + "FROM\n"
                + "subject_schedules AS ss\n"
                + "INNER JOIN subject_offerings AS so ON so.subject_offering_id = ss.subject_offering_id\n"
                + "INNER JOIN instructors AS i ON i.instructor_id = ss.instructor_id\n"
                + "INNER JOIN colleges AS c ON c.college_id = so.college_id\n"
                + "INNER JOIN subject_students AS sjt ON sjt.subject_schedule_id = ss.subject_schedule_id\n"
                + "INNER JOIN students AS s ON s.student_id = sjt.student_id\n"
                + "INNER JOIN program_revisions ON s.program_revision_id = program_revisions.program_revision_id\n"
                + "INNER JOIN programs ON program_revisions.program_id = programs.program_id\n"
                + "WHERE\n"
                + "so.subject_offering_status = 1 \n"
                + "AND s.student_visible=1\n"
                + "ORDER BY ss.subject_code";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        SubjectStudent subjectStudent;
        while (rs.next()) {
            subjectStudent = new SubjectStudent();
            subjectStudent.setSubjectStudentID(rs.getInt("subject_student_id"));
            subjectStudent.setSubjectOfferingID(rs.getInt("subject_offering_id"));
            subjectStudent.setSubjectScheduleID(rs.getInt("subject_schedule_id"));
            subjectStudent.setScheduleStatus(rs.getInt("schedule_status"));
            subjectStudent.setSubjectID(rs.getInt("subject_id"));
            subjectStudent.setInstructorID(rs.getInt("instructor_id"));
            subjectStudent.setSubjectOfferingName(rs.getString("subject_offering_name"));
            subjectStudent.setSubjectOfferingFullName(rs.getString("college_code") + " - " + rs.getString("subject_offering_name") + " - " + rs.getString("subject_code") + " : " + rs.getString("instructor_full_name"));
            subjectStudent.setInstructorFullName(rs.getString("instructor_full_name"));
            subjectStudent.setSubjectSchedule(rs.getString("subject_schedule"));
            subjectStudent.setSubjectCode(rs.getString("subject_code"));
//            subjectStudent.setInstructorFirstName(rs.getString("instructor_first_name"));
//            subjectStudent.setInstructorMiddleName(rs.getString("instructor_middle_name"));
//            subjectStudent.setInstructorLastName(rs.getString("instructor_last_name"));
            subjectStudent.setStudentID(rs.getInt("student_id"));
            subjectStudent.setProgramRevisionName(rs.getString("program_revision_name"));
            subjectStudent.setProgramCode(rs.getString("program_code"));
            subjectStudent.setStudentIDNumber(rs.getString("student_idno"));
//            subjectStudent.setStudentFirstName(rs.getString("student_first_name"));
//            subjectStudent.setStudentMiddleName(rs.getString("student_middle_name"));
//            subjectStudent.setStudentLastName(rs.getString("student_last_name"));
            subjectStudent.setStudentFullName(rs.getString("student_full_name"));
            classList.add(subjectStudent);
        }
        closeConnection(getConnectionObject());
        return classList;
    }

    public static int add(int subjectScheduleID, int studentID) throws Exception {
        try {
            String sql = "INSERT INTO subject_students(subject_schedule_id,student_id) values(?,?)";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, subjectScheduleID);
            preparedStatement.setInt(2, studentID);
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }

    public static int update(int subjectScheduleID, int studentID, int subjectStudentID) throws Exception {
        try {
            String sql = "UPDATE subject_students SET subject_schedule_id=?,student_id=? WHERE subject_student_id=?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, subjectScheduleID);
            preparedStatement.setInt(2, studentID);
            preparedStatement.setInt(3, subjectStudentID);
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }

    public static int delete(int subjectStudentID) throws Exception {
        try {
            String sql = "DELETE FROM subject_students WHERE subject_student_id=?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, subjectStudentID);
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }

    public static int delete(int subjectScheduleID, int studentID) throws Exception {
        int result = 0;
        try {
            String sql = "DELETE FROM subject_evaluations WHERE subject_schedule_id=? and student_id=?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, subjectScheduleID);
            preparedStatement.setInt(2, studentID);
            log.info(preparedStatement.toString());
            if (preparedStatement.executeUpdate() > 0) {
                sql = "DELETE FROM subject_evaluation_suggestions WHERE subject_schedule_id=? and student_id=?";
                PreparedStatement preparedStatement1 = getConnectionObject().prepareStatement(sql);
                preparedStatement1.setInt(1, subjectScheduleID);
                preparedStatement1.setInt(2, studentID);
                result = preparedStatement1.executeUpdate();
            }

            return result;
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }
}
